from urllib import request
from urllib import error
from bs4 import BeautifulSoup
import re
import json
import pymysql

def getHtml(url):
    '''
    获取网页内容
    '''
    try:
        response = request.urlopen(url,timeout=3)
    except error.URLError as err:
        print(err)
    html = response.read().decode('utf-8')
    return html

class dbfund:
    '''
    基金操作类
    '''
    __conn=False
    def __init__(self):
        self.__conn = pymysql.connect(host='localhost',
                             user='root',
                             password='123456',
                             db='funddb',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
    def __del__(self):
        self.__conn.close()

    def Query(self,year,symbol):
        sql="select * from fund_value_info where YEAR(funddate)={} and fund_symbol='{}' order by funddate;".format(year,symbol)
        datalist = []
        try:
            cursor = self.__conn.cursor()    
            cursor.execute(sql)
            datalist = cursor.fetchall()

        except Exception as err:
            print(err)
        return datalist

    def Single(self,symbol):
        sql = "select * from fund_info where fund_symbol='{}'".format(symbol)
        data = ''
        try:
            cursor = self.__conn.cursor()
            cursor.execute(sql)
            data = cursor.fetchone()
        except Exception as err:
            print(err)
        return data
    def Insert(self,model,symbol):
        # print(model)
        try:
            sql = "insert into fund_value_info(fundvalue,fundyield,funddate,fund_symbol) values('{}','{}','{}','{}');".format(model['jjjz'],model['yield'],model['fbrq'],symbol)
            cursor = self.__conn.cursor()
            cursor.execute(sql)
            self.__conn.commit()
            return  cursor.rowcount
        except Exception as err:
            print(err)
            self.__conn.rollback()
            return 0
    def Exists(self,symbol):
        try:
            sql = "select count(*) as count from fund_info where fund_symbol='{}';".format(symbol)
            cursor = self.__conn.cursor()
            cursor.execute(sql)
            self.__conn.commit()
            result = cursor.fetchone()
            return int(result['count'])
        except Exception as err:
            print(err)
            self.__conn.rollback()
            return 0
    def InsertFund(self,model):
        try:
            sql = "insert into fund_info(fund_symbol,fundname) values('{}','{}');".format(model['fundcode'],model['fundname'])
            cursor = self.__conn.cursor()
            cursor.execute(sql)
            self.__conn.commit()
            return  cursor.rowcount
        except Exception as err:
            print(err)
            self.__conn.rollback()
            return 0

def insert(historyList,symbol):
    db = dbfund()
    insertCount = 0
    for i in range(0,len(historyList)-1):
        if i == len(historyList)-1:
            pass
         
        fundyield = (float(historyList[i]['jjjz']) - float(historyList[i+1]['jjjz'])) / float(historyList[i+1]['jjjz']) * 100
        historyList[i]['yield'] = str(round(fundyield,3))
        insertCount += db.Insert(historyList[i],symbol=symbol)
    print('成功插入{}条数据'.format(insertCount))

def getFund(fund_code):
    fundUrl = 'http://finance.sina.com.cn/fund/quotes/{}/bc.shtml'.format(fund_code)
    fundHtml = getHtml(fundUrl)
    soup = BeautifulSoup(fundHtml,features="lxml")
    title = soup.find('h1',class_='fund_name').get_text()
    fund = {'fundcode': fund_code, 'fundname': title}
    #检查是否存在
    
    db = dbfund()
    isExists = db.Exists(fund_code)
    # print(isExists)
    if isExists == 0:
        insertCount = db.InsertFund(fund)
        print('检测到基金数据' if insertCount > 0 else '写入数据库失败')
    else:
        print('数据库中已经存在')
        return
def getStocks(fundcode):
    '''
    获取基金持仓数据
    '''
    fundUrl = 'http://finance.sina.com.cn/fund/quotes/{}/bc.shtml'.format(fund_code)
    
    
def getHistory(fundcode):
    '''
    初始化历史数据
    '''
    print('开始抓取基金历史数据')
    db = dbfund()
    isExists = db.Exists(fund_code)
    # print(isExists)
    if isExists > 0:
        print('数据已存在，跳过抓取')
        return
    url = ''
    # urlHistory = 'https://stock.finance.sina.com.cn/fundInfo/api/openapi.php/CaihuiFundInfoService.getNav?callback=jQuery111203771134280689896_1592611616636&symbol=005827&datefrom=&dateto=&page={}&_=1592611616657'
    urlHistory = 'https://stock.finance.sina.com.cn/fundInfo/api/openapi.php/CaihuiFundInfoService.getNav?symbol={}&page={}'
    pageindex = 1
    
    datalist = []
    historyList = []
    while pageindex < 32:
        
        url = urlHistory.format(fundcode,pageindex)
        jsonStr = getHtml(url)
        
        # print(jsonStr)
        datalist.append(jsonStr)
        pageindex += 1
    
    for item in datalist:
            data = json.loads(item)
            tempList = data['result']['data']['data']
            if len(tempList)>20:
                tempList.pop(20)
            historyList.extend(tempList)
    print(len(historyList))
    insert(historyList,fundcode)

def strategy(symbol,year):
    db = dbfund()
    datalist = db.Query(year,symbol)
    # print(datalist)
    # return
    print('\r\n')
    fund = db.Single(symbol)
    fundname = fund['fundname']
    print('抓取{} {}年数据进行运算'.format(fundname,year))
    
    print("1.定投 2.低点一次买入  3.按涨跌幅买入,无初始资金 4.按涨跌幅买入，有初始资金")
    action = int(input('请输入买入方式：'))
    inputVal = int(input("请输入买入金额:"))
    if action==1:
        strategy1(datalist,inputVal)
    elif action == 2:
        strategy2(datalist,inputVal)
    elif action ==3:
        strategy3(datalist,inputVal,0)
    elif action ==4:
        strategy3(datalist,inputVal,1)
    # 按涨跌幅买入卖出
 
def strategy1(datalist,inputVal):
    print('\r\n')
    pieceMoney = round(inputVal / len(datalist),0) * 5
    print('定投计划：每周买入{}元'.format(pieceMoney))
    assets = 0
    timer = 0
    # 计算定投
    for i in range(0,len(datalist)-1):
        if i % 5 == 0:
            timer += 1
            assets += pieceMoney
            # print('第{}周：{}'.format(timer,round(money1,2)))
        rante = float(datalist[i]['fundyield']) / 100
        assets = round(assets * (1 + rante),2)
            
    print('定投一年，买入{}次，总成本{}，一年后资产为{}'.format(timer,pieceMoney*timer,round(assets,2)))

def strategy2(datalist,inputVal):
    print('\r\n')
    # 计算一次买入
    print('买入策略：一次性买入')
    assets = 0

    for i in range(0,len(datalist)-1):
        rante = float(datalist[i]['fundyield'])/100
        if rante<-0.01 and assets == 0:
            assets = inputVal
        else:
            assets = assets * (1 + rante)
    print('一次买入{},一年后资产为{}'.format(inputVal,round(assets,2)))

def strategy3(datalist,inputVal,firstBuy):
    print('\r\n')
    if firstBuy!= 0:
        firstBuy = int(input('请输入初始资金：'))
    print('买入策略：按涨跌幅买入卖出,初始资金：{}'.format(firstBuy))
    assets = 0 #资产
    timer = 0
    pieceMoney = 2000
    invested = assets
    isClear = False
    # beforeClear = 0
    sole = 0.0
    soleProfit = 0.0
    # 计算定投
    for i in range(0,len(datalist)-1):
        item = datalist[i]
        rante = float(item['fundyield']) / 100
        if(item['funddate']=='2019-03-25 00:00:00'):
            flag = False
        last = assets
        assets = round(assets * (1 + rante),2)
        print('{} 当日净值 {} ,当日涨幅 {}% , 当日收益 {} 元'.format(item['funddate'],item['fundvalue'],round(rante*100,3),round(assets - last,2)))
        if rante >= 0.02 and invested >= 10000:
            #beforeClear = assets / 2 
            sole = assets / 2
            soleProfit += (assets - invested)/2 #4个点清仓
            assets = assets - sole
            invested -= sole
            isClear = True
            print('{} 涨幅{}%,清仓一半，当前资金：{}'.format(datalist[i]['funddate'],round(rante*100,3),round(assets,2)))
        if invested < inputVal:
            buy = 0
            if rante <= -0.01:
                
                if isClear:
                    if rante <= -0.02:
                        buy = sole
                        assets += sole
                        invested += (sole - soleProfit)
                        soleProfit = 0 
                        isClear = False
                        timer += 1
                    #print('第{}次操作，{} 买入{},涨幅{}%,当前资金：{},总投入{}'.format(timer,datalist[i]['funddate'],buy,round(rante,3),round(assets,2),invested))    
                else:
                    if assets ==0 and firstBuy>0:
                        assets = firstBuy
                        timer += 1
                        invested += firstBuy
                        print('{} 初次买入,涨幅{}，买入{}'.format(item['funddate'],round(rante*100,3),assets))
                    else:
                        buy = round(abs(rante) * 100 * 1000,0)
                        timer += 1
                        assets += buy
                        invested += buy
                print('第{}次操作，{} 买入{}涨幅{}%,当前资金：{},总成本{}'.format(timer,datalist[i]['funddate'],buy,round(rante*100,3),round(assets,2),round(invested,2)))
        
        
    print('按涨跌幅买入,总买入{}元，一年后资产为{}'.format(round(invested,2),round(assets,2)))

def main(msg="="*30+"基金模拟计算系统"+"="*30):
    print(msg)
    fundcode = input('请输入基金代码')
    print('开始查询基金数据')
    
    # action = input('1.抓取基金数据  2.计算基金收益 3.退出\r\n' )
    # if action == '1':
    #     fundcode = input('请输入基金代码:')
    #     getHistory(fundcode)
    # elif action == '2':
    #     fundcode = input('请输入基金代码(005827,206009):')
    #     year = input('请输入年份(2018-2020):')
    #     strategy(fundcode,year) 
    #     main('请选择您要进行的操作')
    # elif action == '3':
    #     print('byebye')
    # else:
    #     main('输入错误，请重试')


# main()
getFund('206009')

